
--************************************************
--XML 자료 저장하기 저장프로시저
--************************************************
--EXEC X_YU_2018_XML '2016','2005-0216','tttt'
CREATE  PROC [dbo].[X_YU_2018_XML]
		  @DT VARCHAR(4)	--2018년도
		 ,@ME_ID VARCHAR(10) --사번
		 ,@YU_NO VARCHAR(10) --번호   예) YU01  -> 의료비		
AS 
SET NOCOUNT ON

--XML 자료를 저장하기 한다
			SELECT * 
			FROM YU_2018_XML_TB
			WHERE DT=@DT
					AND ME_ID=@ME_ID	
					AND YU_NO=@YU_NO	

SET NOCOUNT OFF







GO

--************************************************************************************************
--XML 자료를 각 해당되는 필드별로 분리 작업 하는 저장프로시저입니다.
--2018년도에는 카드쪽이 변경되었네요
--************************************************************************************************
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--************************************************
--XML 자료를 나열하기
--************************************************
--EXEC X_YU_2018_XML;2 '2016','2005-0216','tttt'
CREATE  PROC [dbo].[X_YU_2018_XML];2
		  @DT VARCHAR(4)	
		 ,@ME_ID VARCHAR(10)	
		 ,@YU_NO VARCHAR(10)		
AS 
SET NOCOUNT ON

			DECLARE @XML XML
					,@ME_JU_NO VARCHAR(20)  --본인주민번호
			SELECT @ME_JU_NO =REPLACE ( DBO.fn_Me_JU_NO(@ME_ID),'-','')

			SELECT @XML = DU_XML
			FROM YU_2018_XML_TB
			WHERE DT=@DT
					AND ME_ID=@ME_ID
					AND YU_NO=@YU_NO	



--***************************************************************************************************
--한번 전체 지우기 합니다
--***************************************************************************************************
			DELETE YU_2018_PDF_TB 
			WHERE DT=@DT 
				AND ME_ID=@ME_ID



--여기부터 분리가 시작됩니다.
--********************************************************************************
--XML YU01   보험료  'YU01  [보험료]'
--********************************************************************************
			INSERT YU_2018_PDF_TB
			SELECT   
					  @DT  AS DT
					, @ME_ID AS ME_ID
					,'YU01  [보험료]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드	   --SU5
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid      --주민번호   --이름  SU6
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name       --사용자명   SU7
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd     --자료코드 장애인 보장성 보험은 'G0002'   SU8
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid     --사업자번호 ' 1208132969   --SU9
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm --상호 --SU10
					, M.ITEM.value('./@acc_no', 'VARCHAR(100)') AS acc_no	  --증권번호 --SU11
					, M.Item.query('./goods_nm').value('.', 'VARCHAR(100)') AS goods_nm  --보험종류  AS AMT	 --SU12
					--, M.ITEM.value('./@goods_nm', 'VARCHAR(100)') AS goods_nm	  --보험종류

					, M.Item.query('./insu1_resid').value('.', 'VARCHAR(100)') AS  insu1_resid		--주민등록번호_주피보험자
					, M.Item.query('./insu1_nm').value('.', 'VARCHAR(100)')  AS insu1_nm			--성명_주피보험자
					, M.Item.query('./insu2_resid_1').value('.', 'VARCHAR(100)')  AS insu2_resid_1	--주민등록번호_종피보험자_1
					, M.Item.query('./insu2_nm_1').value('.', 'VARCHAR(100)')  AS insu2_nm_1		--성명_종피보험자_1
					, M.Item.query('./insu2_resid_2').value('.', 'VARCHAR(100)')  AS insu2_resid_2	--주민등록번호_종피보험자_2
					, M.Item.query('./insu2_nm_2').value('.', 'VARCHAR(100)')  AS insu2_nm_2		--성명_종피보험자_2
					, M.Item.query('./insu2_resid_3').value('.', 'VARCHAR(100)')  AS insu2_resid_3	--주민등록번호_종피보험자_3
					, M.Item.query('./insu2_nm_3').value('.', 'VARCHAR(100)')  AS insu2_nm_3		--성명_종피보험자_3
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT								--납입금액계	
					
					,' ' AS SU22	
					,' ' AS SU23
					,' ' AS SU24
					,ISNULL( M.Item.query('./sum').value('.', 'VARCHAR(20)') ,'0')  AS SU25		--※ SU25 번은 해당되는 금액만 별도로 가져오도록 하기 위하여 사용합니다.(모든 항목에 적용)	
			--INTO #YU01_TB		
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'A102Y','A102M')

UNION ALL
--********************************************************************************
--XML YU02   의료비  "YU02  [의료비]"
--********************************************************************************
			SELECT    @DT  AS DT
					, @ME_ID AS ME_ID					
					,'YU02  [의료비]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd		--서식코드 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid			--주민번호   --이름
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name				--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd			--자료코드     G0003:의료비G0025:의료기기 구입비용G0026:안경 또는 콘택트렌즈 구입비용G0027:보청기 또는 장애인 보장구구입비용
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid			--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm		--상호				
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT				--납입금액계		
					
					,' ' AS 	SU12
					,' ' AS 	SU13
					,' ' AS 	SU14
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)'),'0')  AS SU25										
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'B101Y','B101D')

UNION ALL
--1 유,2초,3중,4고,5전문,6대학,7대학원,8보육시설,9기타,C 원격대학,D 학위취득과정,E 시간ㄱ제과정, F 학원, G 체육시설,H 사회 복지시설 ,J 장애인 재활기관, K 발달제화 서비스제공기관
--********************************************************************************
--XML YU03  2016년 귀속  교육비  "YU03  [교육비]"
--********************************************************************************
		SELECT    @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU03  [교육비]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd --서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0004:유초중고(시도교육청),G0006:대학교,G0007:기타(개별제출)
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--학교명
					, M.ITEM.value('./@edu_tp', 'VARCHAR(100)') AS edu_tp		--교육비종류	   1:유치원, 2:초등학교, 3:중학교,4:고등학교, 5:전문대학, 6:대학교,7:대학원, 8:보육시설, C:원격대학,D:학위취득과정, E:시간제과정,F:학원, G:체육시설, H:사회복지시설,J:장애인재활교육기관,K:발달재활서비스제공기관		
					, ' ' AS edu_cl --교육비 구분  		A:일반교육비, B:현장체험학습비	
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT				--납입금액계					
					
					,' ' AS 	SU14
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)') ,'0') AS SU25									
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'C101Y','C101M')

UNION ALL
--********************************************************************************
--[추가]  XML YU23   교육비  "YU23  [교육비]"   2017년 귀속 부터
--********************************************************************************
			SELECT    @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU03  [2017귀속교육비]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd --서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0004:유초중고(시도교육청),G0006:대학교,G0007:기타(개별제출)
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--학교명
					, M.ITEM.value('./@edu_tp', 'VARCHAR(100)') AS edu_tp		--교육비종류	   1:유치원, 2:초등학교, 3:중학교,4:고등학교, 5:전문대학, 6:대학교,7:대학원, 8:보육시설, C:원격대학,D:학위취득과정, E:시간제과정,F:학원, G:체육시설, H:사회복지시설,J:장애인재활교육기관,K:발달재활서비스제공기관		
					, M.ITEM.value('./@edu_cl', 'VARCHAR(100)') AS edu_cl --교육비 구분  		A:일반교육비, B:현장체험학습비					
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT				--납입금액계	
					,' ' AS 	SU14
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)') ,'0') AS SU25									
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'C102Y','C102M')

UNION ALL
--********************************************************************************
--XML YU04   직업훈련비  "YU04  [직업훈련비]"
--********************************************************************************
				SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU04  [직업훈련비]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0005:직업훈련비
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--교육기관명

					, M.Item.query('./course_cd').value('.', 'VARCHAR(100)') AS  course_cd		--과정코드
					, M.Item.query('./subject_nm').value('.', 'VARCHAR(100)') AS  subject_nm	--과정명
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT				--납입금액계											
											
					,' ' AS 	SU14
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)'),'0')  AS SU25														
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'C202Y','C202M' )

UNION ALL
--********************************************************************************
--XML YU05   교복구입비  "YU05  [교복구입비]"
--********************************************************************************
				SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU05  [교복구입비]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0024:중고생 교복 구입비용
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT			--납입금액계		
					
					,' ' AS 	SU12
					,' ' AS 	SU13
					,' ' AS 	SU14
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)') ,'0')  AS SU25			
													
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'C301Y','C301M' )
UNION ALL
		--********************************************************************************
		--[추가] XML YU25   학자금 대출
		--********************************************************************************
				SELECT   @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU25  [학자금대출]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0024:중고생 교복 구입비용
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT			--납입금액계		
					
					,' ' AS 	SU12
					,' ' AS 	SU13
					,' ' AS 	SU14
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)') ,'0')  AS SU25			
													
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'C401Y','C401M' )
UNION ALL
--********************************************************************************
--XML YU06   개인연금저축  "YU06  [개인연금저축]"
--********************************************************************************
				SELECT  @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU06  [개인연금저축]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0009:개인연금저축
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호

					, M.ITEM.value('./@acc_no', 'VARCHAR(100)') AS acc_no		--계좌/증권번호

					, M.Item.query('./start_dt').value('.', 'VARCHAR(100)') AS start_dt		--계약시작일
					, M.Item.query('./end_dt').value('.', 'VARCHAR(100)') AS end_dt		--계약종료일
					, M.Item.query('./com_cd').value('.', 'VARCHAR(100)') AS com_cd		--금융회사등 코드
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT			--납입금액계						
					
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)')  ,'0') AS SU25													
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'D101Y','D101M' )
UNION ALL
--********************************************************************************
--XML YU07   연금저축  "YU07  [연금저축]"
--********************************************************************************
				SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU07  [연금저축]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0010:연금저축
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, M.ITEM.value('./@acc_no', 'VARCHAR(100)') AS acc_no		--계좌/증권번호

					, M.Item.query('./com_cd').value('.', 'VARCHAR(100)') AS com_cd					--금융회사등 코드
					, M.Item.query('./ann_tot_amt').value('.', 'VARCHAR(20)') AS ann_tot_amt				--당해연도납입금액
					, M.Item.query('./tax_year_amt').value('.', 'VARCHAR(20)') AS tax_year_amt			--당해연도인출금액
					, M.Item.query('./ddct_bs_ass_amt').value('.', 'VARCHAR(20)')  AS ddct_bs_ass_amt		--순납입금액		
					--, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT	--납입금액계	 (여기에 값이 없음)						
					
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./ddct_bs_ass_amt').value('.', 'VARCHAR(20)') ,'0') AS SU25   -- ddct_bs_ass_amt		--순납입금액									
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'E102Y','E102M' )
UNION ALL
--********************************************************************************
--XML YU08   퇴직연금  "YU08  [퇴직연금]"
--********************************************************************************
			SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU08  [퇴직연금]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0011:퇴직연금
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, M.ITEM.value('./@acc_no', 'VARCHAR(100)') AS acc_no		--계좌번호

					, M.Item.query('./com_cd').value('.', 'VARCHAR(100)') AS com_cd					--금융회사등 코드
					, M.Item.query('./pension_cd').value('.', 'VARCHAR(100)') AS pension_cd			--계좌유형       11:근로자퇴직급여보장법    12:과학기술인공제

					, M.Item.query('./ann_tot_amt').value('.', 'VARCHAR(20)') AS ann_tot_amt				--당해연도납입금액
					, M.Item.query('./tax_year_amt').value('.', 'VARCHAR(20)') AS tax_year_amt			--당해연도인출금액
					, M.Item.query('./ddct_bs_ass_amt').value('.', 'VARCHAR(20)')  AS ddct_bs_ass_amt		--순납입금액		
					--, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT								--납입금액계	  (XXXXX)	
						
						
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./ddct_bs_ass_amt').value('.', 'VARCHAR(20)') ,'0') AS SU25  --ddct_bs_ass_amt		--순납입금액										
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'F102Y','F102M' )
UNION ALL
--********************************************************************************
--XML YU09   신용카드  "YU09  [신용카드]"    2018년도 부터 변경되었습니다.
--********************************************************************************

			--당해년도 자료 가져오기 한다
			SELECT   DISTINCT @DT  AS DT
					, @ME_ID AS ME_ID	
					, 'YU09  [신용카드]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID  --SU2
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0012:신용카드
					, M.Item.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.Item.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, M.Item.value('./@use_place_cd', 'VARCHAR(100)') AS use_place_cd		--종류  1: 일반, 2: 전통시장,3: 대중교통(2013년귀속부터 해당) 4:도서 공연비
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT	

					,' ' AS 	SU13
					,' ' AS 	SU14
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(M.Item.query('./sum').value('.', 'VARCHAR(20)') ,'0') AS SU25						
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			--WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'G106Y','G106M' )    
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'G107Y','G107M' )     --201년 귀속 신용카드변경 
UNION ALL
--********************************************************************************
--XML YU10   현금영수증  "YU10  [현금영수증]"   2018년도 부터 변경되었습니다.
--********************************************************************************			
			SELECT  DISTINCT    @DT  AS DT
					, @ME_ID AS ME_ID
					,'YU10  [현금영수증]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0013:현금영수증
					,'' as busnid
					,'' as trade_nm
					, M.Item.value('./@use_place_cd', 'VARCHAR(100)') AS use_place_cd		--종류  1: 일반, 2: 전통시장,3: 대중교통(2013년귀속부터 해당) 4: 도서 공연비
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT					
					,' ' AS 	SU13
					,' ' AS 	SU14
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24							
					, ISNULL( M.Item.query('./sum').value('.', 'VARCHAR(20)'),'0')  AS SU25  --AMT							--공제대상금액합계								
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			--WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'G206M' )
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'G207M' )  --2018년 귀속 현금 영수증 변경  G206M ->  G207M
UNION ALL
--********************************************************************************
--XML YU11   직불카드  "YU11  [직불카드]"   2018년도 부터 변경되었습니다.
--********************************************************************************
			SELECT  DISTINCT   @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU11  [직불카드]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0014:직불카드등
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, M.ITEM.value('./@use_place_cd', 'VARCHAR(100)') AS use_place_cd		--종류  1: 일반, 2: 전통시장,3: 대중교통(2013년귀속부터 해당), 4 도서 공연비
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT							--공제대상금액합계		
					,' ' AS 	SU13
					,' ' AS 	SU14
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24						
					,ISNULL( M.Item.query('./sum').value('.', 'VARCHAR(20)') ,'0') AS  SU25  --AMT							--공제대상금액합계												
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			--WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'G306Y','G306M' )
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'G307Y','G307M' )  --2018년 귀속 직불 카드 변경

UNION ALL
--********************************************************************************
--XML YU12   주택차입원리금상환  "YU12  [주택차입원리금상환]"
--********************************************************************************
			SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU12  [주택차입원리금상환]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0016:주택임차차입금 원리금상환
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, M.ITEM.value('./@acc_no', 'VARCHAR(100)') AS use_place_cd		--종류  1: 일반, 2: 전통시장,3: 대중교통(2013년귀속부터 해당)

					, M.Item.query('./goods_nm').value('.', 'VARCHAR(100)') AS goods_nm		--상품명
					, M.Item.query('./lend_dt').value('.', 'VARCHAR(20)') AS lend_dt				--대출일			
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT						--상환액계	
					
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)') ,'0') AS SU25  --AMT						--상환액계									
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'J101Y','J101M' )

UNION ALL
--********************************************************************************
--XML YU13   장기저당차입이자  "YU13  [장기저당차입이자]"
--********************************************************************************
			SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU13  [장기저당차입이자]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드   G0017:장기주택저당차입금 이자상환액
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--상호
					, M.ITEM.value('./@acc_no', 'VARCHAR(100)') AS acc_no		--계좌번호 

					, M.Item.query('./lend_kd').value('.', 'VARCHAR(100)') AS lend_kd					--대출종류     1:무주택자의 중도금 대출2:기존주택구입3:주택분양권 대출
					, M.Item.query('./house_take_dt').value('.', 'VARCHAR(100)') AS house_take_dt		--주택취득일
					, M.Item.query('./mort_setup_dt').value('.', 'VARCHAR(100)') AS mort_setup_dt		--저당권설정일
					, M.Item.query('./start_dt').value('.', 'VARCHAR(100)') AS start_dt					--최초차입일
					, M.Item.query('./end_dt').value('.', 'VARCHAR(100)') AS end_dt						--최종상환예정일
					, M.Item.query('./repay_years').value('.', 'VARCHAR(100)') AS repay_years			--상환기간연수
					, M.Item.query('./lend_goods_nm').value('.', 'VARCHAR(100)') AS lend_goods_nm		--상품명

					, M.Item.query('./debt').value('.', 'VARCHAR(20)')  AS debt							--차입금
					, M.Item.query('./fixed_rate_debt').value('.', 'VARCHAR(20)')  AS fixed_rate_debt		--고정금리차입금
					, M.Item.query('./not_defer_debt').value('.', 'VARCHAR(20)')  AS not_defer_debt		--비거치식상환차입금
					, M.Item.query('./this_year_rede_amt').value('.', 'VARCHAR(20)')  AS his_year_rede_amt	--당해년 원금상환액
					,'0'   -- M.ITEM.value('./@ddct', 'VARCHAR(100)') AS  ddct							--소득공제대상액   
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS SU24							--연간합계액	
						
					,ISNULL( M.Item.query('./sum').value('.', 'VARCHAR(20)')  ,'0') AS SU25          --소득공제 대상액  --AS use_place_cd																	
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'J203Y','J203M' )

UNION ALL
--********************************************************************************
--XML YU14   주택마련저축  "YU14  [주택마련저축]"
--********************************************************************************
			SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU14  [주택마련저축]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드  G0018:주택마련저축
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--취급기관
					, M.ITEM.value('./@acc_no', 'VARCHAR(100)') AS acc_no		--계좌번호

					, M.Item.query('./goods_nm').value('.', 'VARCHAR(100)') AS goods_nm			--저축명
					, M.Item.query('./saving_gubn').value('.', 'VARCHAR(100)') AS saving_gubn	--저축구분  1:청약저축,2:주택청약종합저축,4:근로자주택마련저축			
					, M.Item.query('./reg_dt').value('.', 'VARCHAR(100)')  AS reg_dt			--가입일자	
					, M.Item.query('./com_cd').value('.', 'VARCHAR(100)')  AS com_cd			--금융회사등 코드					
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT							--납입금액계	
											
				
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)') ,'0')  AS SU25 -- AMT							--납입금액계					
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'J301Y','J301M' )
UNION ALL
--********************************************************************************
--XML YU15   전세이자상환  "YU15  [전세이자상환]"
--********************************************************************************
			SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU15  [전세이자상환]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드  G0023:목돈 안드는 전세 이자상환액
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--취급기관
					, M.ITEM.value('./@acc_no', 'VARCHAR(100)') AS use_place_cd		--계좌번호

					, M.Item.query('./lend_loan_amt').value('.', 'VARCHAR(100)') AS lend_loan_amt	--대출원금
					, M.Item.query('./lend_dt').value('.', 'VARCHAR(100)') AS lend_dt	--대출일자										
					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT					--연간합계액	
							
							
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)')  ,'0')  AS 	SU25										
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'J401Y','J401M' )

UNION ALL
--********************************************************************************
--XML YU16   소상공인공제부금
--********************************************************************************			
			SELECT    @DT  AS DT
					, @ME_ID AS ME_ID	
					,'YU16  [소상공인공제부금]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('.././@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드  G0023:목돈 안드는 전세 이자상환액
					, M.ITEM.value('.././@acc_no', 'VARCHAR(100)') AS use_place_cd		--공제계약번호/증서번호

					, M.Item.query('.././start_dt').value('.', 'VARCHAR(100)') AS start_dt			--대상기간시작일
					, M.Item.query('.././end_dt').value('.', 'VARCHAR(100)') AS end_dt				--대상기간종료일	
					, M.Item.query('.././pay_method').value('.', 'VARCHAR(100)') AS pay_method		--납입방법					
					, M.ITEM.value('./@ddct', 'VARCHAR(100)') AS ddct					--소득공제대상액	
					, M.Item.query('../sum').value('.', 'VARCHAR(20)')  AS AMT					--납입금액계

					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL( M.ITEM.value('./@ddct', 'VARCHAR(100)'),'0') AS ddct					--소득공제대상액									
			FROM @XML.nodes('/yesone/form/man/data/sum') AS M(ITEM)
			WHERE M.Item.value('../../../@form_cd', 'VARCHAR(100)') in ( 'K101M' )


--			--EXEC X_YU_2018_XML;2 '2015','2005-0216','tttt'
UNION ALL
--********************************************************************************
--XML YU17   기부금  "YU17  [기부금]"
--********************************************************************************
			SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU17  [기부금]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드  G0023:목돈 안드는 전세 이자상환액
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--취급기관
					, M.ITEM.value('./@donation_cd', 'VARCHAR(100)') AS donation_cd		--기부유형 10:법정기부금,20:정치자금기부금,21:진흥기금출연,40:지정기부금,41:종교단체기부금,42:우리사주기부금 (조특법 제 88조의 4),50:기타기부금

					, M.Item.query('./sum').value('.', 'VARCHAR(20)')  AS AMT					--공제대상기부금(실제 공제 금액이된다)
					, M.Item.query('./sbdy_apln_sum').value('.', 'VARCHAR(20)')  AS G_AMT					--기부장려금
					, M.Item.query('./conb_sum').value('.', 'VARCHAR(20)')  AS T_AMT					--기부금액 합계		
					,' ' AS 	SU15
					,' ' AS 	SU16
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./sum').value('.', 'VARCHAR(20)') ,'0') AS	SU25					
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'L102Y','L102D')
			
UNION ALL
--********************************************************************************
--XML YU18   장기증권저축  "YU18  [장기증권저축]"   --
--********************************************************************************
			SELECT     @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU18  [장기집합투자증권]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('./@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드  G0029:장기집합투자증권저축
					, M.ITEM.value('./@busnid', 'VARCHAR(100)') AS busnid		--사업자번호
					, M.ITEM.value('./@trade_nm', 'VARCHAR(100)') AS trade_nm	--취급기관
					, M.ITEM.value('./@secu_no', 'VARCHAR(100)') AS secu_no		--계좌번호


					, M.Item.query('./fund_nm').value('.', 'VARCHAR(100)') AS fund_nm			--펀드명
					, M.Item.query('./reg_dt').value('.', 'VARCHAR(100)') AS reg_dt				--가입일자	
					, M.Item.query('./com_cd').value('.', 'VARCHAR(100)') AS com_cd		--금융회사등 코드	
					, M.Item.query('./sum').value('.', 'VARCHAR(20)') AS amt					--연간합계액	
					, M.Item.query('./ddct_bs_ass_amt').value('.', 'VARCHAR(20)')  AS ddct_bs_ass_amt					--소득공제대상금액
									
									
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					,ISNULL(  M.Item.query('./ddct_bs_ass_amt').value('.', 'VARCHAR(20)') ,'0') AS 	SU25				
			FROM @XML.nodes('/yesone/form/man/data') AS M(ITEM)
			WHERE M.Item.value('../../@form_cd', 'VARCHAR(100)') in ( 'N101Y','N101M' )



UNION ALL
--********************************************************************************
--XML YU19   건강보험  "YU19  [건강보험]"   --
--********************************************************************************

			SELECT    @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU19  [건강보험]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('.././@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드  G0029:장기집합투자증권저축
					, '' AS SU9
					, '' AS SU10
					
					, M.Item.value('./@hi_yrs', 'VARCHAR(100)')  AS SU11			--연말정산 건강
					, M.Item.value('./@ltrm_yrs', 'VARCHAR(100)')  AS SU12			--연말정산 요양
					, M.Item.value('./@hi_ntf', 'VARCHAR(100)')  AS SU13			--건강합계
					, M.Item.value('./@ltrm_ntf', 'VARCHAR(100)')  AS SU14			--요양합계
					, M.Item.value('./@hi_pmt', 'VARCHAR(100)')  AS SU15			--건강합계3
					, M.Item.value('./@ltrm_pmt', 'VARCHAR(100)')  AS SU16			--요양합계3				
					, M.Item.query('../sum').value('.', 'VARCHAR(20)') AS SU17					--전체 합계( 연말건강,연말요양은 제외됨)
									
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					, M.Item.query('../sum').value('.', 'VARCHAR(20)') AS SU25					--전체 합계( 연말건강,연말요양은 제외됨)															
			FROM @XML.nodes('/yesone/form/man/data/sum') AS M(ITEM)
			WHERE M.Item.value('../../../@form_cd', 'VARCHAR(100)') in ( 'O101M')


UNION ALL
--********************************************************************************
--XML YU20   건강보험  "YU20  [국민연금]"   --
--********************************************************************************

			SELECT    @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU20  [국민연금]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('.././@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드  G0029:장기집합투자증권저축
					, '' AS SU9
					, '' AS SU10
					, ' ' AS sp_ntf			--추가납부금액--2017에 있는것임
					, M.Item.value('./@spym', 'VARCHAR(100)')  AS SU11			--추가납부금액 
					, M.Item.value('./@jlc', 'VARCHAR(100)')  AS SU12			--실업크레딧납부금액
					, M.Item.value('./@ntf', 'VARCHAR(100)')  AS SU13			--합계
					, M.Item.value('./@pmt', 'VARCHAR(100)')  AS SU14			--총합계						
					, M.Item.query('../sum').value('.', 'VARCHAR(20)') AS SU15					--전체 합계( 연말건강,연말요양은 제외됨)
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					, M.Item.query('../sum').value('.', 'VARCHAR(20)') AS SU25					--전체 합계( 연말건강,연말요양은 제외됨)															
			FROM @XML.nodes('/yesone/form/man/data/sum') AS M(ITEM)
			WHERE M.Item.value('../../../@form_cd', 'VARCHAR(100)') in ( 'P101M' )
UNION ALL
--********************************************************************************
--XML YU20  2017 건강보험  "YU20  [국민연금]"   --
--********************************************************************************

			SELECT    @DT  AS DT
					, @ME_ID AS ME_ID		
					,'YU20  [2017국민연금]' AS  YU_NO
					,'1'   AS SUNBUN
					, 1    AS GU_ID
					, M.Item.value('../../../@form_cd', 'VARCHAR(100)') AS form_cd	--서식코드		 
					, LEFT( M.Item.value('../../@resid', 'VARCHAR(20)'),6)  +'-'+SUBSTRING( M.Item.value('../../@resid', 'VARCHAR(20)'),7,7)  AS resid		--주민번호   
					, M.Item.value('../../@name', 'VARCHAR(100)')  AS name			--성명
					, M.Item.value('.././@dat_cd', 'VARCHAR(100)') AS dat_cd		--자료코드  G0029:장기집합투자증권저축
					, '' AS SU9
					, '' AS SU10
					, M.Item.value('./@sp_ntf', 'VARCHAR(100)')  AS sp_ntf			--추가납부금액 
					, M.Item.value('./@spym', 'VARCHAR(100)')  AS SU12			--추가납부금액 
					, M.Item.value('./@jlc', 'VARCHAR(100)')  AS SU13			--실업크레딧납부금액
					, M.Item.value('./@ntf', 'VARCHAR(100)')  AS SU14			--합계
					, M.Item.value('./@pmt', 'VARCHAR(100)')  AS SU15			--총합계						
					, M.Item.query('../sum').value('.', 'VARCHAR(20)') AS SU16				--전체 합계( 연말건강,연말요양은 제외됨)
					,' ' AS 	SU17
					,' ' AS 	SU18
					,' ' AS 	SU19
					,' ' AS 	SU20
					,' ' AS 	SU21
					,' ' AS 	SU22
					,' ' AS 	SU23
					,' ' AS 	SU24
					, M.Item.query('../sum').value('.', 'VARCHAR(20)') AS SU25					--전체 합계( 연말건강,연말요양은 제외됨)															
			FROM @XML.nodes('/yesone/form/man/data/sum') AS M(ITEM)
			WHERE M.Item.value('../../../@form_cd', 'VARCHAR(100)') in ('P102M' )

			--SELECT * FROM YU_2018_PDF_TB

SET NOCOUNT OFF
